﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;


public class DataSetToExcel
{
    public DataSetToExcel() { }

    public void Convert(DataSet oDS, HttpResponse Response)
    {
        Response.Clear();
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        System.IO.StringWriter oSW = new System.IO.StringWriter();
        HtmlTextWriter oHW = new HtmlTextWriter(oSW);
        DataGrid oDG = new DataGrid();
        oDG.DataSource = oDS.Tables[0];
        oDG.DataBind();
        oDG.RenderControl(oHW);
        Response.Write(oSW.ToString());
        Response.Flush();
        Response.Close();
    }

    public static void ExportResult(DataSet ds, string filename)
    {
        if (ds == null) return;
        if (filename == null)
        {
            filename = "file";
        }
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "GB2312";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        if (ds.Tables[0].Rows.Count > 0)
        {
            HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
        }
        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
        StringWriter stringWrite = new StringWriter(myCItrad);
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        for (int i = 0; i < ds.Tables.Count; i++)
        {
            DataGrid dg = new DataGrid();
            dg.DataSource = ds.Tables[i];
            dg.ShowHeader = false;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
        }
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename='" + filename + "'.xls");

        HttpContext.Current.Response.Write(stringWrite.ToString());
        HttpContext.Current.Response.End();
    }

    public static void OutExcel(DataSet ds, string tableName)
    {
        if (ds == null) return;
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Charset = "UTF-8";
        if (!String.IsNullOrEmpty(tableName))
        {
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(tableName) + ".xls");
        }
        else
        {
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=file.xls");
        }
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//设置输出流为简体中文
        HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        if (ds.Tables[0].Rows.Count > 0)
        {
            HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content='text/html; charset=UTF-8'>");
        }
        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
        System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
        AlternatingStyle.BackColor = System.Drawing.Color.FromArgb(233, 234, 244);
        headerStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.Font.Bold = true;
        headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
        itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;

        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        for (int i = 0; i < ds.Tables.Count; i++)
        {

            DataGrid dg = new DataGrid();
            dg.DataSource = ds.Tables[i];
            dg.ShowHeader = false;
            dg.DataBind();
            dg.RenderControl(oHtmlTextWriter);
        }
        HttpContext.Current.Response.Write(oStringWriter.ToString());
        HttpContext.Current.Response.End();
    }

    public static void OutExcel(DataTable table, string tableName)
    {
        if (table == null) return;
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Charset = "UTF-8";
        if (!String.IsNullOrEmpty(tableName))
        {
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(tableName) + ".xls");
        }
        else
        {
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=file.xls");
        }
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//设置输出流为简体中文
        HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        if (table.Rows.Count > 0)
        {
            HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content='text/html; charset=UTF-8'>");
        }

        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
        System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
        AlternatingStyle.BackColor = Color.FromArgb(233, 234, 244);
        headerStyle.BackColor = Color.LightGray;
        headerStyle.Font.Bold = true;
        headerStyle.HorizontalAlign = HorizontalAlign.Center;
        itemStyle.HorizontalAlign = HorizontalAlign.Center;

        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

        DataGrid dg = new DataGrid();
        dg.DataSource = table;
        dg.ShowHeader = false;
        dg.DataBind();
        dg.RenderControl(oHtmlTextWriter);
        HttpContext.Current.Response.Write(oStringWriter.ToString());
        HttpContext.Current.Response.End();
    }

    public static void DataSetOutExcel(DataSet ds, string FileName)
    {

        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.BufferOutput = true;
        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(FileName) + ".xls");
        HttpContext.Current.Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
        HttpContext.Current.Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
        HttpContext.Current.Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
        //定义标题样式    
        HttpContext.Current.Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12' ss:Color='#000000' ss:Bold='1'/></Style>");//title颜色和样式

        //定义边框
        HttpContext.Current.Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
      <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");

        HttpContext.Current.Response.Write("</Styles>");
        //SheetCount代表生成的 Sheet 数目。
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            DataTable t = ds.Tables[i];
            HttpContext.Current.Response.Write("<Worksheet ss:Name='" + t.TableName + "'>");
            HttpContext.Current.Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");

            //输出标题
            HttpContext.Current.Response.Write("<Row ss:AutoFitHeight='1'>");
            System.Collections.Generic.Dictionary<int, string> col = new System.Collections.Generic.Dictionary<int, string>();
            for (int j = 0; j < t.Columns.Count; j++)
            {
                HttpContext.Current.Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + t.Columns[j].ColumnName + "</Data></Cell>");
                col.Add(j, t.Columns[j].DataType.Name);
            }

            HttpContext.Current.Response.Write("</Row>");


            for (int j = 0; j < t.Rows.Count; j++)
            {
                HttpContext.Current.Response.Write("<Row>");
                for (int l = 0; l < t.Columns.Count; l++)
                {
                    string colType = col[l];
                    string format = "";
                    switch (colType)
                    {
                        case "Int32":
                        case "Int16":
                        case "Decimal":
                            format = "Number";
                            break;
                        default:
                            format = "String";
                            break;
                    }
                    HttpContext.Current.Response.Write("<Cell ss:StyleID='border'><Data ss:Type='" + format + "'>" + ds.Tables[i].Rows[j][l].ToString() + "</Data></Cell>");
                }
                HttpContext.Current.Response.Write("</Row>");
            }
            HttpContext.Current.Response.Write("</Table>");
            HttpContext.Current.Response.Write("</Worksheet>");
            HttpContext.Current.Response.Flush();
        }
        HttpContext.Current.Response.Write("</Workbook>");
        HttpContext.Current.Response.End();

    }


    public static void DataSetOutExcel(DataTable ds, string FileName)
    {

        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.BufferOutput = true;
        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(FileName) + ".xls");
        HttpContext.Current.Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
        HttpContext.Current.Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
        HttpContext.Current.Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
        //定义标题样式    
        HttpContext.Current.Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12' ss:Color='#000000' ss:Bold='1'/></Style>");//title颜色和样式

        //定义边框
        HttpContext.Current.Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
      <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");

        HttpContext.Current.Response.Write("</Styles>");
        //SheetCount代表生成的 Sheet 数目。
        DataTable t = ds;
        HttpContext.Current.Response.Write("<Worksheet ss:Name='" + t.TableName + "'>");
        HttpContext.Current.Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");

        //输出标题
        HttpContext.Current.Response.Write("<Row ss:AutoFitHeight='1'>");
        System.Collections.Generic.Dictionary<int, string> col = new System.Collections.Generic.Dictionary<int, string>();
        for (int j = 0; j < t.Columns.Count; j++)
        {
            HttpContext.Current.Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + t.Columns[j].ColumnName + "</Data></Cell>");
            col.Add(j, t.Columns[j].DataType.Name);
        }

        HttpContext.Current.Response.Write("</Row>");


        for (int j = 0; j < t.Rows.Count; j++)
        {
            HttpContext.Current.Response.Write("<Row>");
            for (int l = 0; l < t.Columns.Count; l++)
            {
                string colType = col[l];
                string format = "";
                switch (colType)
                {
                    case "Int32":
                    case "Int16":
                    case "Decimal":
                        format = "Number";
                        break;
                    default:
                        format = "String";
                        break;
                }
                HttpContext.Current.Response.Write("<Cell ss:StyleID='border'><Data ss:Type='" + format + "'>" + ds.Rows[j][l].ToString() + "</Data></Cell>");
            }
            HttpContext.Current.Response.Write("</Row>");
        }
        HttpContext.Current.Response.Write("</Table>");
        HttpContext.Current.Response.Write("</Worksheet>");
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.Write("</Workbook>");
        HttpContext.Current.Response.End();

    }

}
